package com.ciei.dpagm.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ciei.dpagm.common.enums.ErrorCodeEnum;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.validation.constraints.NotNull;
import java.io.*;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtils{
    public static String NO_DEFINE = "no_define";//未定义的字段
    public static String DEFAULT_DATE_PATTERN="yyyy年MM月dd日";//默认日期格式
    public static int DEFAULT_COLOUMN_WIDTH = 17;

    public static final String EXCEL_2003L =".xls";

    public static final String EXCEL_2007U =".xlsx";

    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     * @param title 标题行
     * @param headMap 属性-列头
     * @param jsonArray 数据集
     * @param datePattern 日期格式，传null值则默认 年月日
     * @param colWidth 列宽 默认 至少17个字节
     * @param out 输出流
     */
    public static void exportExcelX(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {
        if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);
         //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);
        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
//        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  office打开黑色单元格的罪魁祸首，给单元格填充了黑色
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);
        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
//        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  office打开黑色单元格的罪魁祸首，给单元格填充了黑色
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet();
        //设置列宽
        int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter
                .hasNext();) {
            String fieldName = iter.next();

            properties[ii] = fieldName;
            headers[ii] = headMap.get(fieldName);

            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] =  bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii,arrColWidth[ii]*256);
            ii++;
        }
        SXSSFRow titleRow = (SXSSFRow) sheet.createRow(0);//表头 rowIndex=0
        titleRow.createCell(0).setCellValue(title);
        titleRow.getCell(0).setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
        SXSSFRow headerRow = (SXSSFRow) sheet.createRow(1); //列头 rowIndex =1
        // 填充表头
        for(int i=0;i<headers.length;i++)
        {
            headerRow.createCell(i).setCellValue(headers[i]);
            headerRow.getCell(i).setCellStyle(headerStyle);

        }
        //数据内容从 rowIndex=2开始
        int rowIndex = 2;
        // 遍历集合数据，产生数据行
        for (Object obj : jsonArray) {
            if(rowIndex == 65535 || rowIndex == 0){
                if ( rowIndex != 0 ) sheet = (SXSSFSheet) workbook.createSheet();//如果数据超过了，则在第二页显示


            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++)
            {
                SXSSFCell newCell = (SXSSFCell) dataRow.createCell(i);

                Object o =  jo.get(properties[i]);
                String cellValue = "";
                if(o==null) cellValue = "";
                else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
                else if(o instanceof Float || o instanceof Double)
                    cellValue= new BigDecimal(o.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
                else cellValue = o.toString();

                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);

                // 手动设置单元格宽度
                sheet.setColumnWidth(i, arrColWidth[i] * 256);
            }
            rowIndex++;
        }
        // 自动调整宽度
        /*for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }*/
        try {
            workbook.write(out);
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static void exportExcel(){

    }

    /**
     * 获取Excel单元格内容
     * @param cell  单元格
     * @return
     */
    public static String getCellValue(Cell cell, @NotNull SimpleDateFormat sdf) {
        String cellValue;
        // 判断数据的类型
        switch (cell == null ? Cell.CELL_TYPE_BLANK  : cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                // 数字
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    cellValue = sdf.format(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
            case Cell.CELL_TYPE_STRING:
                // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                // 布尔型
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case Cell.CELL_TYPE_FORMULA:
                // 公式
                cellValue = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_BLANK:
                // 空值
            case Cell.CELL_TYPE_ERROR:
                // 故障
            default:
                cellValue = "";
                break;
        }
        return cellValue;
    }

    /**
     * 判断Excel 行是否为空行
     * @param row   行
     * @return
     */
    public static boolean isRowEmpty(Row row) {
        if (row == null || StringUtils.isEmpty(row.toString())) {
            return true;
        } else {
            Iterator<Cell> it = row.iterator();
            boolean isEmpty = true;
            while (it.hasNext()) {
                Cell cell = it.next();
                if (cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    isEmpty = false;
                    break;
                }
            }
            return isEmpty;
        }
    }



    /**获取表格的合并列
     * @param sheet
     * @return
     */
    public static List<CellRangeAddress> getCombineCell(Sheet sheet){
        List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();
        int sheetMergedRegions = sheet.getNumMergedRegions();
        for (int i=0;i<sheetMergedRegions;i++){
            CellRangeAddress cellRangeAddress = sheet.getMergedRegion(i);
            cellRangeAddressList.add(cellRangeAddress);
        }
        return cellRangeAddressList;
    }

    /**获取合并项的数据
     * @param cellRangeAddressList
     * @param cell
     * @param sheet
     * @param simpleDateFormat
     * @return
     */
    public static String getCombineCellValue( List<CellRangeAddress> cellRangeAddressList,Cell cell,Sheet sheet,SimpleDateFormat simpleDateFormat){
        String cellValue = "";
        JSONObject isMergedRegionJson = isMergedRegion(cellRangeAddressList, cell.getRowIndex(), cell.getColumnIndex());
        Boolean isMergedRegion = isMergedRegionJson.getBoolean(APP.SUCCESS);
        if (isMergedRegion){
            JSONObject cellIndexJson = isMergedRegionJson.getJSONObject(APP.DATA);
            Integer rowIndex = cellIndexJson.getInteger("rowIndex");
            Integer columnIndex = cellIndexJson.getInteger("columnIndex");
            Row row = sheet.getRow(rowIndex);
            Cell mergedRegionCell = row.getCell(columnIndex);
            cellValue = ExcelUtils.getCellValue(mergedRegionCell, simpleDateFormat);
        }

        return cellValue;
    }


    /**是不是合并项
     * @param cellRangeAddressList
     * @param rowIndex
     * @param columnIndex
     * @return
     */
    public static JSONObject isMergedRegion( List<CellRangeAddress> cellRangeAddressList,Integer rowIndex,Integer columnIndex){
        int firstColumnIndex = 0;
        int lastColumnIndex = 0;
        int firstRowIndex = 0;
        int lastRowIndex = 0;
        for (CellRangeAddress ca:cellRangeAddressList){
            firstColumnIndex = ca.getFirstColumn();
            lastColumnIndex = ca.getLastColumn();
            firstRowIndex = ca.getFirstRow();
            lastRowIndex = ca.getLastRow();
            if(rowIndex >= firstRowIndex && rowIndex <= lastRowIndex){
                if(columnIndex >= firstColumnIndex && columnIndex <= lastColumnIndex) {
                    HashMap<String, Object> cellIndexMap = new HashMap<>();
                    cellIndexMap.put("rowIndex",firstRowIndex);
                    cellIndexMap.put("columnIndex",lastColumnIndex);
                    return JsonUtil.getSuccess("true").fluentPut(APP.DATA,cellIndexMap);
                }
            }
        }
        return JsonUtil.getFail(ErrorCodeEnum.DATA_NOT_FOUND,"false");
    }

    /**获取Workbook
     * @param filePath
     * @param baseFilePath
     * @return
     */
    public static Workbook getWorkbook(String filePath,String baseFilePath){
        filePath = filePath.replaceFirst("uploads/", "");
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        if (!filePath.endsWith(EXCEL_2003L) && !filePath.endsWith(EXCEL_2007U)) {
            return null;
        }
        File file = new File(baseFilePath + filePath);
        if (!file.exists()) {
            return null;
        }
        try (FileInputStream fis = new FileInputStream(file)){
            Workbook workbook;
            // 根据Excel文件创建工作簿
            if (filePath.endsWith(EXCEL_2003L)) {
                workbook = new HSSFWorkbook(fis);
            } else {
                workbook = new XSSFWorkbook(fis);
            }
            return workbook;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }

    /**
     * 设置标题名
     * @param workbook Excel对象
     * @param sheet
     * @param titleName 标题名
     **/
    public static void setTitleRow(@NotNull HSSFWorkbook workbook,@NotNull HSSFSheet sheet,String titleName){
        HSSFCellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short)16);
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        titleFont.setFontName("宋体");
        titleCellStyle.setFont(titleFont);
        HSSFRow titleRow = sheet.createRow(0);
        titleRow.setHeightInPoints(40);
        HSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(titleName);
        titleCell.setCellStyle(titleCellStyle);
    }

    /**
     * 设置标题名
     * @param workbook Excel对象
     * @param sheet
     * @param titleName 标题名
     **/
    public static void setTitleRow(@NotNull SXSSFWorkbook workbook ,@NotNull Sheet sheet,String titleName){
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short)16);
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        titleFont.setFontName("宋体");
        titleCellStyle.setFont(titleFont);
        Row titleRow = sheet.createRow(0);
        titleRow.setHeightInPoints(40);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(titleName);
        titleCell.setCellStyle(titleCellStyle);
    }

    /**
     * 获取列名行样式
     * @param workbook Excel对象
     **/
    public static CellStyle getColumnNameCellStyle(@NotNull Workbook workbook){
        CellStyle columnNameCellStyle = workbook.createCellStyle();
        columnNameCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        columnNameCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        columnNameCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        columnNameCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        columnNameCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        columnNameCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        columnNameCellStyle.setWrapText(true);
        Font columnNameFont = workbook.createFont();
        columnNameFont.setFontHeightInPoints((short)12);
        columnNameFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        columnNameFont.setFontName("宋体");
        columnNameCellStyle.setFont(columnNameFont);
        return columnNameCellStyle;
    }

    /**
     * 获取列名行样式
     * @param workbook Excel对象
     **/
    public static HSSFCellStyle getColumnNameCellStyle(@NotNull HSSFWorkbook workbook){
        HSSFCellStyle columnNameCellStyle = workbook.createCellStyle();
        columnNameCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        columnNameCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        columnNameCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        columnNameCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        columnNameCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        columnNameCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        columnNameCellStyle.setWrapText(true);
        HSSFFont columnNameFont = workbook.createFont();
        columnNameFont.setFontHeightInPoints((short)12);
        columnNameFont.setFontName("宋体");
        columnNameCellStyle.setFont(columnNameFont);
        return columnNameCellStyle;
    }

    /**
     * 获取内容行样式
     * @param workbook
     **/
    public static CellStyle getCenterContentCellStyle(@NotNull Workbook workbook){
        Font contentFont = workbook.createFont();
        contentFont.setFontHeightInPoints((short)12);
        contentFont.setFontName("宋体");
        CellStyle centerContentCellStyle = workbook.createCellStyle();
        centerContentCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        centerContentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        centerContentCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        centerContentCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        centerContentCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        centerContentCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        centerContentCellStyle.setWrapText(true);
        centerContentCellStyle.setFont(contentFont);
        return centerContentCellStyle;
    }

    /**
     * 获取居中内容行样式
     * @param workbook
     **/
    public static HSSFCellStyle getCenterContentCellStyle(@NotNull HSSFWorkbook workbook){
        HSSFCellStyle textCellStyle = getTextCellStyle(workbook);
        textCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        textCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        textCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        textCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        textCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        return textCellStyle;
    }

    /**
     * 获取内容行样式
     * @param workbook
     **/
    public static HSSFCellStyle getTextCellStyle(@NotNull HSSFWorkbook workbook){
        HSSFFont contentFont = workbook.createFont();
        contentFont.setFontHeightInPoints((short)12);
        contentFont.setFontName("宋体");
        HSSFCellStyle textContentCellStyle = workbook.createCellStyle();
        textContentCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        textContentCellStyle.setWrapText(true);
        textContentCellStyle.setFont(contentFont);
        return textContentCellStyle;
    }

    /**
     * 获取内容行样式
     * @param workbook
     * @return
     */
    public static CellStyle getTextCellStyle(@NotNull Workbook workbook){
        CellStyle columnNameCellStyle = workbook.createCellStyle();
        columnNameCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        columnNameCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        columnNameCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        columnNameCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        columnNameCellStyle.setWrapText(true);
        return columnNameCellStyle;
    }

    /**
     * 获取标题单元格样式
     * @param workbook
     * @return
     */
    public static CellStyle getTitleCellStyle(@NotNull Workbook workbook){
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 生成文件流
     * @param response
     * @param workbook
     * @param excelName 导出的excel文件名
     **/
    public static void generateOutputStream(HttpServletResponse response, @NotNull HSSFWorkbook workbook, String excelName){
        try (OutputStream os = response.getOutputStream()) {
            response.setContentType("application/vnd.ms-excel;");
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(excelName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
            response.addHeader("Cache-Control", "no-cache");
            workbook.write(os);
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 设置表格列内容
     * @param sheet
     * @param rowContentList 行内容列表
     * @param centerContentCellStyle 居中内容行样式
     * @param contentRowIndex 内容行下标
     **/
    public static void setTableColumnContent(HSSFSheet sheet, List<String> rowContentList, HSSFCellStyle centerContentCellStyle, int contentRowIndex){
        HSSFRow contentRow = sheet.createRow(contentRowIndex);
        for(int cellIndex = 0; cellIndex < rowContentList.size();cellIndex++ ){
            HSSFCell cell = contentRow.createCell(cellIndex);
            cell.setCellStyle(centerContentCellStyle);
            cell.setCellValue(rowContentList.get(cellIndex));
        }
    }

    /**
     * 设置表格列内容
     * @param sheet
     * @param rowContentList 行内容列表
     * @param centerContentCellStyle 居中内容行样式
     * @param contentRowIndex 内容行下标
     **/
    public static void setTableColumnContent(HSSFSheet sheet, List<String> rowContentList, CellStyle centerContentCellStyle, int contentRowIndex){
        HSSFRow contentRow = sheet.createRow(contentRowIndex);
        for(int cellIndex = 0; cellIndex < rowContentList.size();cellIndex++ ){
            HSSFCell cell = contentRow.createCell(cellIndex);
            cell.setCellStyle(centerContentCellStyle);
            cell.setCellValue(rowContentList.get(cellIndex));
        }
    }

    /**
     * 设置表格列名和列宽
     * @param workbook
     * @param sheet
     * @param columnNameList 列名列表
     * @param columnWidthList 列宽列表
     **/
    public static void setTableColumnNameAndColumnWidth(HSSFWorkbook workbook, HSSFSheet sheet, List<String> columnNameList, List<Integer> columnWidthList, int columnRowIndex){
        HSSFRow columnNameRow = sheet.createRow(columnRowIndex);
        columnNameRow.setHeightInPoints(15.75f);
        HSSFCellStyle columnNameCellStyle = getColumnNameCellStyle(workbook);
        for (int i = 0; i < columnNameList.size(); i++) {
            String columnName = columnNameList.get(i);
            HSSFCell columnNameCell = columnNameRow.createCell(i);
            columnNameCell.setCellStyle(columnNameCellStyle);
            columnNameCell.setCellValue(columnName);
        }
        // 设置每列宽度
        for (int i = 0; i < columnNameList.size(); i++) {
            sheet.setColumnWidth(i, (int)(columnWidthList.get(i) + 0.72) * 256);
        }
    }

    /**
     * 设置表格列名和列宽
     * @param workbook
     * @param sheet
     * @param columnNameList 列名列表
     * @param columnWidthList 列宽列表
     **/
    public static void setTableColumnNameAndColumnWidth(SXSSFWorkbook workbook, Sheet sheet, List<String> columnNameList, List<Integer> columnWidthList, int columnRowIndex){
        Row columnNameRow = sheet.createRow(columnRowIndex);
        columnNameRow.setHeightInPoints(15.75f);
        CellStyle columnNameCellStyle = getColumnNameCellStyle(workbook);
        for (int i = 0; i < columnNameList.size(); i++) {
            String columnName = columnNameList.get(i);
            Cell columnNameCell = columnNameRow.createCell(i);
            columnNameCell.setCellStyle(columnNameCellStyle);
            columnNameCell.setCellValue(columnName);
        }
        // 设置每列宽度
        for (int i = 0; i < columnNameList.size(); i++) {
            sheet.setColumnWidth(i, (int)(columnWidthList.get(i) + 0.72) * 256);
        }
    }

    /**
     * sheetName 过滤掉特殊字符
     * @param sheetName
     * @return
     */
    public static String validateSheetName(String sheetName) {
        if (sheetName.length() > 31) {
            sheetName = sheetName.substring(0, 31);
        }
        sheetName=sheetName.replaceAll("[\\[*\\]*/*\n?*:*\\\\]","");
        sheetName=sheetName.replaceAll("^'*|'*$","");
        return sheetName;
    }

    /**
     * 导入一个excel文件读取指定工作表的数据
     * @param file 上传的Excel文件
     * @param sheetIndex 工作表下标
     * @param startRow 表头行数
     * @return 返回一个JSONObject对象，包含是否成功、错误信息、数据
     * throws RuntimeException
     */
    public static JSONObject readExcel(MultipartFile file, Integer sheetIndex, Integer startRow) {
        try (InputStream inputStream = file.getInputStream()) {
            Workbook workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(sheetIndex); // 获取传入参数决定的工作表
            // 获取数据
            JSONArray data = getData(sheet, startRow);
            return JsonUtil.getSuccess("").fluentPut(APP.DATA, data);
        } catch (IOException e) {
            // 处理异常
            // 直接抛出异常，让上层处理
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取数据
     * @param sheet 工作表
     * @param startRow 开始行
     * @return
     */
    private static JSONArray getData(Sheet sheet, Integer startRow) {
        JSONArray data = new JSONArray();
        // 数据从第二行开始
        for (int rowIndex = startRow+1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            // 将数据对象添加到 JSONArray 中
            data.add(row);
        }
        return data;
    }





}

